﻿DECLARE @Var_ReservationDate VARCHAR(100);
DECLARE @Var_DeptCode VARCHAR(100);
DECLARE @Var_DoctCode VARCHAR(100);
--号源过滤标志: 0:所有 1：只返回有剩余号源的医师
DECLARE @Var_HideEmptyDoct BIT;

SET @Var_ReservationDate = @ReservationDate_In;
SET @Var_DeptCode = @DeptCode_In;
SET @Var_DoctCode = @DoctCode_In;
SET @Var_HideEmptyDoct = @HideEmptyDoct_In;

IF ( @Var_ReservationDate ) <> '%'
    AND ( ( ( @Var_ReservationDate ) <= GETDATE() )
          OR ( ( @Var_ReservationDate ) > GETDATE() + 7 )
        )
    BEGIN
        SELECT  -1 AS RESULT ,
                'ReservationDate超出范围！' AS RESULTTEXT;
        RETURN;
    END;

SELECT  0 AS RESULT ,
        '' AS RESULTTEXT ,
        RTRIM(A.KSCode) AS SecondDeptCode ,
        RTRIM(A.KSName) AS SecondDeptName ,
        RTRIM(A.YSCode) AS DoctCode ,
        RTRIM(A.YSName) AS DoctName ,
        B.YHFLAG AS TitleCode ,
        CASE YHFLAG
          WHEN 2 THEN '普通医师（初职）'
          WHEN 3 THEN '主任医师（高职）'
          WHEN 5 THEN '主治医师（中职）'
          WHEN 6 THEN '副主任医师（副高）'
        END AS TitleName ,
        RTRIM(A.CheckGroupCode) AS ClinicCode ,
        RTRIM(A.CheckGroupName) AS ClinicName ,
        A.CheckMoney1 AS RegFee ,
        A.CheckMoney2 AS ClinicFee ,
        RTRIM(A.TimeInterval) AS TimeIntervalCode ,
        RTRIM(A.Specialty) AS Specialty ,
        '' AS Descripton,
        A.totNum AS TotalSourceCount ,
        A.Address ,
        CASE WHEN A.totNum > 0
             THEN CASE WHEN ISNULL(A.totNum2, 0) > ISNULL(C.alreadycount, 0)
                       THEN ISNULL(A.totNum2, 0) - ISNULL(C.alreadycount, 0)
                       ELSE 0
                  END
             ELSE 999
        END AS RemainSourceCount
FROM    MZSchedule A ( NOLOCK )
        LEFT JOIN ( SELECT  DoctCode ,
                            COUNT(*) AS alreadycount
                    FROM    AP_ReservationSheet (NOLOCK)
                    WHERE   DelDate IS NULL
                            AND ReservationDate = CAST(@Var_ReservationDate AS DATE)
                    GROUP BY DoctCode
                  ) C ON A.YSCode = C.DoctCode
        LEFT JOIN YSCODE B ( NOLOCK ) ON C.DoctCode = B.CODE
WHERE   A.KSCode = @Var_DeptCode
        AND A.YSCode LIKE @Var_DoctCode
        AND A.YSCode <> '*'
        AND ( A.StopEndDate IS NULL
              OR @Var_ReservationDate < A.StopBegDate
              OR @Var_ReservationDate > A.StopEndDate
            )
        AND A.Weekday = DATEPART(WEEKDAY, ( @Var_ReservationDate ))
        AND ( Status IS NULL
              OR Status = 2
            )
        AND ( ( @Var_HideEmptyDoct ) = 0
              OR ( ( @Var_HideEmptyDoct ) = 1
                   AND ( CASE WHEN A.totNum > 0
                              THEN CASE WHEN ISNULL(A.totNum2, 0) > ISNULL(C.alreadycount,
                                                              0) THEN 1
                                        ELSE 0
                                   END
                              ELSE 1
                         END = 1 )
                 )
            );
         
      